﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Lamex.mNC.Common
{
    public class GetDataTable
    {
        //获取跟进表数据【订单时间】
        public static DataTable Get_OrderData(string Product_TS)
        {
            DataTable dt = new DataTable();
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(Product_TS))
            {
                sqlWhere += " and DE.Product_TS like '%" + Product_TS + "%'";
            }
            string sql = @" select distinct de.Order_Number,DR.MODULE_NAME,de.Part_No,de.Drawing_No,sum(de.Part_QTY) Part_QTY FROM [LamexMESManager].[dbo].[Transfer_Part_Detailed] DE
 JOIN [LamexMESManager].[dbo].[IE_Part_Routing] RO on RO.Part_No=DE.Part_No
 JOIN [LamexMESManager].[dbo].[IE_Routing_Combination] CO ON RO.GROUP_CODE=CO.GROUP_CODE AND CO.ROUTING_CODE like '%P05%' 
 LEFT JOIN [LamexMESManager].[dbo].[NC_PN_AL_Detail_Drawing] DR ON DR.DRAWING_NO=DE.Drawing_No
 LEFT JOIN [LamexMESManager].[dbo].[NC_PN_AL_Manufacture_Convert_Record] CR ON CR.PART_NO=DE.Part_No AND CR.FOLLOW_NO=DE.Order_Number 
where 1=1 AND CR.PART_NO IS NULL AND CR.FOLLOW_NO IS NULL " + sqlWhere + "  group by  de.Order_Number,de.Part_No,de.Drawing_No,DR.MODULE_NAME ORDER BY DE.Order_Number";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取跟进表的图纸号和数量
        public static DataTable Get_OrderDataByDwgNo(string PartNo, string OrderNo)
        {
            string sql = @"select distinct de.Order_Number,DR.MODULE_NAME,de.Part_No,de.Drawing_No,sum(de.Part_QTY) Part_QTY FROM [LamexMESManager].[dbo].[Transfer_Part_Detailed] DE
 JOIN [LamexMESManager].[dbo].[IE_Part_Routing] RO on RO.Part_No=DE.Part_No
 JOIN [LamexMESManager].[dbo].[IE_Routing_Combination] CO ON RO.GROUP_CODE=CO.GROUP_CODE AND CO.ROUTING_CODE like '%P05%' 
 LEFT JOIN [LamexMESManager].[dbo].[NC_PN_AL_Detail_Drawing] DR ON DR.DRAWING_NO=DE.Drawing_No
 where DE.Order_Number='" + OrderNo + "' and DE.PART_NO='" + PartNo + @"'
group by  de.Order_Number,de.Part_No,de.Drawing_No,DR.MODULE_NAME ORDER BY DE.Order_Number";
            DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        //获取物料号和图纸号
        public static DataTable Get_Part_Detailed(string Order_Number)
        {
            DataTable dt = new DataTable();
            string sql = @"select DISTINCT Part_No, Drawing_No FROM [LamexMESManager].[dbo].[Transfer_Part_Detailed] 
                           where Order_Number='" + Order_Number + "' ";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取图纸号
        public static DataTable Get_Part_Detailed_Dwg(string Order_Number, string PartNo)
        {
            DataTable dt = new DataTable();
            string sql = @"select Drawing_No FROM [LamexMESManager].[dbo].[Transfer_Part_Detailed] 
                           where Order_Number='" + Order_Number + "' AND Part_No='" + PartNo + "' ";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //取NC_PN_AL_FeatData_Creo与Transfer_Part_Detailed数据【正式】
        public static DataTable Get_FeatData_PartDetail(string PartNO, string DWG_NO, string WORK_SURFACE)
        {
            DataTable dt = new DataTable();
            string sql_worksurface = WORK_SURFACE.Length > 0 ? " and f.WORK_SURFACE='" + WORK_SURFACE + "'" : "";
            string sql = @"SELECT distinct [FILE_NAME]
      ,[DWG_NO]
      ,[MODULE_CODE]
      ,f.[PART_NO]
      ,[PART_LENGTH]
      ,[WORK_SURFACE]
      ,[FACE_WIDTH]
      ,[FACE_HEIGHT]
      ,[UDF_NAME]
      ,[FEATURE_TYPE]
      ,[DEEP_CODE]
      ,[COORD_X]
      ,[COORD_Y]
      ,[COORD_Z]
      ,[FEATURE_DIA]
      ,[SLOT_LENGTH]
      ,[SLOT_WIDTH]
      ,[FILLIT_RADIUS]    
  FROM [LamexMESManager].[dbo].[NC_PN_AL_FeatData_Creo] f
  join [LamexMESManager].[dbo].[Transfer_Part_Detailed] p on f.PART_NO=p.Part_No and f.DWG_NO=p.Drawing_No
  where f.PART_NO='" + PartNO + "' AND  f.DWG_NO='" + DWG_NO + "' " + sql_worksurface + "";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //取NC_PN_AL_FeatData_Creo与Transfer_Part_Detailed数据【测试】
        public static DataTable Get_FeatData_PartDetail_TEST(string PartNO, string DWG_NO, string WORK_SURFACE)
        {
            DataTable dt = new DataTable();
            string sql_worksurface = WORK_SURFACE.Length > 0 ? " and f.WORK_SURFACE='" + WORK_SURFACE + "'" : "";
            string sql = @"SELECT distinct [FILE_NAME]
      ,[DWG_NO]
      ,[MODULE_CODE]
      ,f.[PART_NO]
      ,[PART_LENGTH]
      ,[WORK_SURFACE]
      ,[FACE_WIDTH]
      ,[FACE_HEIGHT]
      ,[UDF_NAME]
      ,[FEATURE_TYPE]
      ,[DEEP_CODE]
      ,[COORD_X]
      ,[COORD_Y]
      ,[COORD_Z]
      ,[FEATURE_DIA]
      ,[SLOT_LENGTH]
      ,[SLOT_WIDTH]
      ,[FILLIT_RADIUS]    
  FROM [LamexMESManager].[dbo].[NC_PN_AL_FeatData_Creo] f
  where f.PART_NO='" + PartNO + "' AND  f.DWG_NO='" + DWG_NO + "' " + sql_worksurface + "";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }

        //取NC_PN_AL_FeatData_Creo与Transfer_Part_Detailed 数量
        public static DataTable Get_FeatData_PartDetail_QTY(string Order_Number)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT 
       [Package_Mo]
      ,[Order_Type]
      ,[Order_QTY]
      ,[Material_No]
      ,[Part_No]
      ,[Part_QTY]
      ,[Order_Number]
      ,[Drawing_No]  
  FROM [LamexMESManager].[dbo].[Transfer_Part_Detailed] where Order_Number='" + Order_Number + "'";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        
        
        
        }
        //获取刀具参数表
        public static DataTable Get_Parameter_Tools()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [TOOL_NO]
      ,[COMPENSATION_NO]
      ,[TOOL_TYPE]
      ,[TOOL_DIA]
      ,[BLADE_LENTH]
      ,[EXPOSED_LENTH]
      ,[ROTATE_SPEED]
      ,[FEED_SPEED_H]
      ,[FEED_SPEED_V]
      ,[UPDATE_TIME]
      ,[UPDATE_USER]
  FROM [LamexMESManager].[dbo].[NC_PN_AL_Parameter_Tools]";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取加工参数表
        public static DataTable Get_Parameter_Machine()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [MODULE_NAME]
      ,[MODULE_CODE]
      ,[CNC_FLAG]
      ,[MODULE_WIDTH]
      ,[MODULE_HEIGHT]
      ,[H1_FEED_D]
      ,[H1_FEED_X]
      ,[H1_DEEP_D]
      ,[H1_DEEP_X]
      ,[H2_FEED_D]
      ,[H2_FEED_X]
      ,[H2_DEEP_D]
      ,[H2_DEEP_X]
      ,[H3_FEED_D]
      ,[H3_FEED_X]
      ,[H3_DEEP_D]
      ,[H3_DEEP_X]
      ,[H4_FEED_D]
      ,[H4_FEED_X]
      ,[H4_DEEP_D]
      ,[H4_DEEP_X]
      ,[UPDATE_TIME]
      ,[UPDATE_USER]
  FROM [LamexMESManager].[dbo].[NC_PN_AL_Parameter_Machine]";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取【NC_PN_AL_FeatData_Creo】
        public static DataTable Get_NC_PN_AL_FeatData_Creo()
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [DWG_NO]
      ,[PART_NO]
   FROM [LamexMESManager].[dbo].[NC_PN_AL_FeatData_Creo]";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取扫描加工当天数量
        public static DataTable Get_TotalToday()
        {
            DataTable dt = new DataTable();
            string sql = @"  SELECT SUM(PROCESS_QTY)AS PROCESS_QTY
  FROM [LamexMESManager].[dbo].[NC_PN_AL_Manufacture_Process_Record]
  WHERE convert(varchar(20),SCAN_TIME,112)= convert(varchar(20),getdate(),112)";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取当天扫描的所有记录
        public static DataTable Get_RecordToday()
        {
            DataTable dt = new DataTable();
            string sql = @"  SELECT BARCODE_NO,PROCESS_QTY
  FROM [LamexMESManager].[dbo].[NC_PN_AL_Manufacture_Process_Record]
  WHERE convert(varchar(20),SCAN_TIME,112)= convert(varchar(20),getdate(),112)";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //获取中间表数据
        public static DataTable GetBarCode(string QRCode)
        {
            DataTable dt = new DataTable();
            string sql = @"SELECT [BARCODE_NO]    
  FROM [LamexMESManager].[dbo].[NC_PN_AL_Manufacture_Process_Record_Middle]
  WHERE BARCODE_NO='" + QRCode + "'";
            dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
        //根据根据部门获取跟进人--NC问题记录
        public static DataTable GetManByDept(string strDept, string strNumber, string strState)
        {
            DataTable dataTable = new DataTable();
            DataTable dataTable1 = new DataTable();

            //获取查询字段名称
            string strName;
            if (Regex.Matches(strNumber, "[a-zA-Z]").Count > 0)
                strName = "DWG_NO";
            else
                strName = "PART_NO";

            string sql;

            if (strDept == "PE_SJ")
            {
                sql = @" SELECT B.NAME FROM [LamexMESManager].[dbo].[NC_Convert_TaskList] A
              JOIN [LamexMESManager].[dbo].[NC_Convert_Issue_Person_In_Charge] B ON A.CREATED_USER=B.SAP_ACCOUNT
              WHERE " + strName + "='" + strNumber + "'";
                dataTable1 = BaseBuildSql.GetDataTableBySql(sql);
            }
            else if (strDept == "NC")
            {
                switch (strState)
                {
                    case "":
                        sql = @" SELECT ISNULL(PLANNING_ENGINEER,'') as NAME FROM [dbo].[NC_Convert_ProcessRecord]
                                   WHERE " + strName + "='" + strNumber + "'";
                        break;
                    case "转码中":
                        sql = @" SELECT ISNULL(LOCKED_USER,'') as NAME FROM [dbo].[NC_Convert_ProcessRecord]
                                   WHERE " + strName + "='" + strNumber + "'";
                        break;
                    default:
                        sql = @" SELECT ISNULL(CONVERTED_ENGINEER,'') as NAME FROM [dbo].[NC_Convert_ProcessRecord]
                                   WHERE " + strName + "='" + strNumber + "'";
                        break;
                }
                dataTable1 = BaseBuildSql.GetDataTableBySql(sql);
            }
            if (dataTable1.Rows.Count > 0)
                sql = @"SELECT ISNULL(NAME,'') NAME FROM NC_Convert_Issue_Person_In_Charge WHERE DEPT_NAME='" + strDept + "'";
            else
                sql = @"SELECT '' NAME 
                        UNION 
                        SELECT ISNULL(NAME,'') NAME FROM NC_Convert_Issue_Person_In_Charge WHERE DEPT_NAME='" + strDept + "'";
            dataTable = BaseBuildSql.GetDataTableBySql(sql);

            dataTable1.Merge(dataTable);
            DataView dataView = new DataView(dataTable1);
            dataTable1 = dataView.ToTable(true, new string[] { "NAME" });
            return dataTable1;
        }
        //获取当前转码状态
        public static string GetState(string strNumber)
        {
            string strState = "";
            string strName;
            if (Regex.Matches(strNumber, "[a-zA-Z]").Count > 0)
                strName = "DWG_NO";
            else
                strName = "PART_NO";
            string sql = @"SELECT ISNULL(NC_STATE,'') NC_STATE FROM [NC_Convert_ProcessRecord] WHERE " + strName+"='"+strNumber+"'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            if(dataTable.Rows.Count>0)
            {
                strState = dataTable.Rows[0]["NC_STATE"].ToString();
            }
            return strState;
        }
        //获取转码类型
        public static string GetConvertType(string strNumber)
        {
            string strState = "";
            string strName;
            if (Regex.Matches(strNumber, "[a-zA-Z]").Count > 0)
                strName = "DWG_NO";
            else
                strName = "PART_NO";
            string sql = @"SELECT ISNULL(NC_TYPE,'') NC_TYPE FROM [LamexMESManager].[dbo].[NC_Convert_TaskList] WHERE " + strName + "='" + strNumber + "'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            if (dataTable.Rows.Count > 0)
            {
                strState = dataTable.Rows[0]["NC_TYPE"].ToString();
            }
            return strState;
        }
        //获取收件人邮箱信息
        public static string GetEmailAddrssee(string strName)
        {
            string list = null;
            string sql = @"SELECT EMAIL FROM NC_Convert_Issue_Person_In_Charge WHERE NAME='" + strName + "'";
            DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
                list = dt.Rows[0]["EMAIL"].ToString();
            return list;
        }
        //获得收件人邮箱信息
        public static string[] GetEmailCC(string strJobName)
        {
            string[] list = null;
            string sql = @"SELECT CC FROM  Sys_Push_Email_Manage WHERE JOB_NAME='"+strJobName+"'";
            DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
            if (dt.Rows.Count > 0)
            {
                list = dt.Rows[0]["CC"].ToString().Split(';');       
            }
            return list;
        }
        public static string GetJobName(string strDept)
        {
            string str = "";
            switch (strDept)
            {
                case "NC":
                    str = "NC_Issue_NC";
                    break;
                case "PE_Desk":
                    str = "NC_Issue_PE_Desk";
                    break;
                case "PE_Panel":
                    str = "NC_Issue_PE_Panel";
                    break;
                case "PE_Seat":
                    str = "NC_Issue_PE_Seat";
                    break;
                case "PE_SJ":
                    str = "NC_Issue_PE_SJ";
                    break;
            }
            return str;
        }
        //获取问题登记记录
        public static DataTable GetNC_Convert_IssueRecord(string strIssue_No)
        {
            string sql = @"SELECT [ISSUE_NO]
      ,[ISSUE_FROM]
      ,[SUBMITTER]
      ,[SUBMIT_TIME]
      ,[PART_OR_DWG_NO]
      ,[ISSUE_CATEGORY]
      ,[ISSUE_DESC]
      ,[MO_PART_QTY]
      ,[RESPONSIBLE_DEPT]
      ,[PERSON_IN_CHARGE]
      ,[REASON_AND_ACTION]
      ,[FINISHED_TIME]
  FROM [LamexMESManager].[dbo].[NC_Convert_IssueRecord]
  WHERE ISSUE_NO='" + strIssue_No + "'";
            DataTable dt = BaseBuildSql.GetDataTableBySql(sql);
            return dt;
        }
    }
}
